Data loading and Cleaning

The packages of tidyverse help imensely with data cleaning and initial visualization. The eurostat package helps parse the open JSON api of eurostat databases.

library("tidyverse")

Loading tidyverse: ggplot2
Loading tidyverse: tibble
Loading tidyverse: tidyr
Loading tidyverse: readr
Loading tidyverse: purrr
Loading tidyverse: dplyr
Conflicts with tidy packages -----------------------------------------------------------------------------------
filter(): dplyr, stats
lag():    dplyr, stats

library("eurostat")
library(readxl)

We now extract and use the dataset from our sources. first the biggest and messiest dataset!

init.data<- data.frame(read.csv("../data/WEF_TTCR17_data_for_download.csv"))
as_tibble(init.data)

init.data

We can see that this is now viewable as a dataframe . We immediately see that there are a lot of columns and values that we just arent interested in. The analysis will entail tourism spending and other characteristics pertaining to 28 the EU member states. First order would be filter out only the relevant 28 states.

colnames(init.data) <- as.character(unlist(init.data[1,]))
init.data

To achieve our goal we first convert the header to meaningful data. We then create a column vector with the relevant columns needed and subset the data to get relevant fields for our 28 member states.

reqCol <- c('Series','Attribute','AUT', 'BEL', 'BGR', 'HRV', 'CYP', 'CZE', 'DNK', 'EST', 'FIN', 'FRA', 'DEU', 'GRC', 'HUN', 'IRL', 'ITA', 'LVA', 'LTU', 'LUX', 'MLT', 'NLD', 'POL', 'PRT', 'ROU', 'SVK', 'SVN','TUR', 'ESP', 'SWE', 'GBR')
prelimData <- init.data[,reqCol]
prelimData

WECTTCI17 <- filter(prelimData,Attribute == 'Value')
WECTTCI17

Statista <- read_excel("../data/statistic_id314340_leading-european-city-tourism-destinations-in-2017-by-number-of-bednights.xlsx",sheet = "Data",skip=4)
names(Statista) <- c('City','Count')
Statista$CountryTwo <- c("GB","FR","DE","IT","ES","ES","CZ","TR","AT","DE","NL","SE","DE","IT","PT")
Statista$CountryThree <- c("GBR","FRA","DEU","ITA","ESP","ESP","CZE","TUR","AUT","DEU","NLD","SWE","DEU","ITA","PRT")
Statista <- Statista[c(4,3,1,2)]
Statista

automated loading of datasets from eurostat to ensure up to date data on Eu residents as updated by the primary source of data collection from the EU

1. Total footfalls in EU.

eutwoletter <- c("AT","BE","BG","HR","CY","CZ","DK","EE","FI","FR","DE","GR","HU","IE","IT","LV","LT","LU","MT","NL","PL","PT","RO","SK","SI","ES","SE","GB")
byFootfall<- get_eurostat("tour_dem_extot", filters = list(nongeo=1,geo = eutwoletter,sinceTimePeriod=2012,purpose="TOTAL",precision=1,duration="N_GE1",unit="THS_EUR",partner="DOM"), time_format = "num")
byFootfall <- select(byFootfall,geo:values)
byFootfall

This data needs to cleaned and transformed into the right structure. The composite primary key we will year and geo.

year <- unique(byFootfall$time)
geo <- unique(byFootfall$geo)
geo <- as.character(geo)
footfall <- c()
for (i in 1:length(geo)) {
  for (j in 1:length(year)) {
    test <- filter(byFootfall,geo==geo[i],year==year[j])%>% select(values)
    row <- c(year[j],geo[i],unlist(test,recursive = TRUE,use.names = FALSE))
    footfall <- rbind(footfall,row)
    
  }
}
colnames(footfall) <- c("Year","Geo","Footfall_in_mil")
write.csv(footfall,file = "Footfall.csv",row.names = FALSE)
footfall <- read.csv("Footfall.csv")
footfall

byAge<- get_eurostat("tour_dem_toage", filters = list(nongeo=1,geo = eutwoletter,sinceTimePeriod=2012,purpose="TOTAL",precision=1,duration="N_GE1",unit="NR",partner="DOM",age=c("Y_LT15","Y15-24","Y25-34","Y35-44","Y45-64","Y_GE65")), time_format = "num")
byAge <- select(byAge,age:values)
byAge

We will need to transform this data into a usabe format.

ageFactor <- unique(byAge$age)
year <- unique(byAge$time)
geo <- unique(byAge$geo)
geo <- as.character(geo)
age <- c()
for (i in 1:length(geo)) {
  for (j in 1:length(year)) {
    test <- filter(byAge,geo==geo[i],year==year[j])%>% select(values)
    row <- c(year[j],geo[i],unlist(test,recursive = TRUE,use.names = FALSE))
    age <- rbind(age,row)
    
  }
}
colnames(age) <- c("Year","Geo","Y15-24","Y25-34","Y35-44","Y45-64","Y_GE65","Y_LT15")
write.csv(age,file = "Age.csv",row.names = FALSE)
age <- read.csv("Age.csv")
age$Y_LT15 <- NULL
age

bySex <- get_eurostat("tour_dem_tosex", filters = list(nongeo=1,geo = eutwoletter,sinceTimePeriod=2012,precision=1,duration="N_GE1",unit="NR",partner="DOM",sex=c("M","F")), time_format = "num")
bySex <- select(bySex,sex:values)
bySex

This datframe also has to be transformed with geo and time to be the composite primary key

sexFactor <- unique(bySex$sex)
year <- unique(bySex$time)
geo <- unique(bySex$geo)
geo <- as.character(geo)
sex <- c()
for (i in 1:length(geo)) {
  for (j in 1:length(year)) {
    test <- filter(bySex,geo==geo[i],year==year[j])%>% select(values)
    row <- c(year[j],geo[i],unlist(test,recursive = TRUE,use.names = FALSE))
    sex <- rbind(sex,row)
    
  }
}
colnames(sex) <- c("Year","Geo","F","M")
write.csv(sex,file = "Sex.csv",row.names = FALSE)
Sex <- read.csv("Sex.csv")
Sex